﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;

namespace IService.DataDLL
{
    /// <summary>
    /// 查询数据库的方法类
    /// </summary>
    internal class SelectMethod : BaseMethod
    {
        private static Hashtable SelectClauseStore = new Hashtable();
        public SelectMethod(Type infoType)
            : base(infoType)
        {
        }
        public SelectMethod(BaseInfo info)
            : base(info)
        {
        }
        public SelectMethod(string TableName, BaseInfo info)
            : base(TableName, info)
        {
        }

        public DataTable Select(string WhereClause, string Order)
        {
            return SelectParam(WhereClause, Order, null);
        }

        public DataTable SelectParam(string WhereClause, string Order, IDataParameter[] paramaters)
        {
            if (base.InfoType != null)
            {
                StringBuilder sb_sql = new StringBuilder(this.SelectClause);
                if (!string.IsNullOrEmpty(WhereClause))
                {
                    sb_sql.AppendFormat(" WHERE {0}", WhereClause);
                }
                if (!string.IsNullOrEmpty(Order))
                {
                    sb_sql.AppendFormat(" ORDER BY {0}", Order);
                }
                sb_sql.Append(';');
                string CommandText = sb_sql.ToString();
                DataTable result = base.ExecuteDataTable(CommandText, paramaters);
                return result;
            }
            return null;
        }

        public DataTable SelectTop(int TopRows)
        {
            return SelectTop(TopRows, null, null);
        }

        /// <summary>
        /// 查询前n行记录
        /// </summary>
        /// <param name="TopRows"></param>
        /// <param name="WhereClause"></param>
        /// <param name="Order"></param>
        /// <returns></returns>
        public DataTable SelectTop(int TopRows, string WhereClause, string Order)
        {
            if (base.InfoType != null)
            {
                StringBuilder sb_selecttop = new StringBuilder();
                string TopClause = TopRows > 0 ? string.Format("TOP {0}", TopRows) : null;
                SelectCommandText(sb_selecttop, TopClause);
                if (!string.IsNullOrEmpty(WhereClause))
                {
                    sb_selecttop.AppendFormat(" WHERE {0}", WhereClause);
                }
                if (!string.IsNullOrEmpty(Order))
                {
                    sb_selecttop.AppendFormat(" ORDER BY {0}", Order);
                }
                sb_selecttop.Append(';');
                string CommandText = sb_selecttop.ToString();
                DataTable result = base.ExecuteDataTable(CommandText, null);
                return result;
            }
            return null;
        }

        public DataRow SelectByIdentity()
        {
            if (!((base.Entity != null) && base.HasIdentity))
            {
                return null;
            }
            DataTable table = this.Select(base.IdentityWhereClause, null);
            if (table == null || table.Rows.Count <= 0)
            {
                return null;
            }
            return table.Rows[0];
        }

        public bool SelectInfoByIdentity()
        {
            DataRow row = SelectByIdentity();
            Hashtable table = base.GetHashTableByDatarow(row);
            return base.Entity.SetInternalData(table);
        }

        /// <summary>
        /// 针对一条记录的查询，需传入一个BaseInfo对象
        /// </summary>
        /// <param name="WhereClause"></param>
        /// <returns></returns>
        public bool SingleSelect(string WhereClause)
        {
            return SingleSelect(WhereClause,null);
        }

        /// <summary>
        /// 针对一条记录的查询，需传入一个BaseInfo对象
        /// </summary>
        /// <param name="WhereClause"></param>
        /// <returns></returns>
        public bool SingleSelect(string WhereClause, IDataParameter[] parameters)
        {
            if (base.Entity == null)
            {
                return false;
            }
            try
            {
                if (WhereClause == null)
                {
                    WhereClause = base.IdentityWhereClause;
                }
                DataTable result = this.SelectParam(WhereClause, null, parameters);
                if (result == null || result.Rows.Count <= 0)
                {
                    return false;
                }
                DataRow row = result.Rows[0];
                Hashtable table = base.GetHashTableByDatarow(row);
                return base.Entity.SetInternalData(table);
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="pe"></param>
        /// <returns></returns>
        public DataTable SelectPaging(PagingEntity pe)
        {
            if (pe.RecordCount <= 0)
            {
                CountMethod count = new CountMethod(base.InfoType);
                pe.RecordCount = count.Count(pe.WhereClause);//SqlHelper.GetRecordCount(pe.TableName, pe.WhereClause);
            }
            int startRecord = (pe.PageIndex - 1) * pe.PageSize;
            int endRecord = pe.PageSize + startRecord;
            int outerTop = Math.Min(pe.PageSize, pe.RecordCount - startRecord);
            int innerTop = outerTop + startRecord;
            StringBuilder sb_paging = new StringBuilder();
            sb_paging.AppendFormat("SELECT * FROM (SELECT TOP {0} * FROM (SELECT TOP {1} {2} FROM [{3}]", new object[] { outerTop, innerTop, pe.SelectString, pe.TableName });
            if (!string.IsNullOrEmpty(pe.WhereClause))
            {
                sb_paging.AppendFormat(" where {0}", pe.WhereClause);
            }
            sb_paging.AppendFormat(" ORDER BY {0}) AS inner_table ORDER BY {1}) AS final_table ORDER BY {0}", pe.AscString, pe.DescString);
            DataTable tb = base.ExecuteDataTable(sb_paging.ToString(), null);//SqlHelper.SelectToDataTable(sb_paging.ToString());
            return tb;
        }

        protected string SelectCommandText(StringBuilder sb_select, string TopClause)
        {
            sb_select.Append("SELECT ");
            if (!string.IsNullOrEmpty(TopClause))
            {
                sb_select.AppendFormat("{0} ", TopClause);
            }
            base.AppendParameterString(sb_select, "[{0}]", ", ", true);
            sb_select.AppendFormat(" FROM [{0}]", base.TableName);
            return sb_select.ToString();
        }
        /// <summary>
        /// select 语句前部
        /// </summary>
        public string SelectClause
        {
            get
            {
                string result = SelectClauseStore[base.TableName] as string;
                if (result == null)
                {
                    StringBuilder sb_select = new StringBuilder();
                    this.SelectCommandText(sb_select, null);
                    SelectClauseStore[base.TableName] = result = sb_select.ToString();
                }
                return SelectClauseStore[base.TableName].ToString();
            }
        }
    }
}
